package jrain.fw.dao.common.runner;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jrain.fw.core.dao.SqlParser;
import jrain.fw.core.dao.SqlRunner;
import jrain.fw.core.dao.TransactionManager;
import jrain.fw.core.dao.bean.PageObj;
import jrain.fw.core.dao.bean.SqlConst;
import jrain.fw.core.dao.bean.SqlInfo;
import jrain.fw.core.dao.handler.IResultSetHandler;
import jrain.fw.core.log.Logger;
import jrain.fw.core.log.LoggerFactory;
import jrain.fw.core.service.ServiceConst;
import jrain.fw.core.utils.FwUtils;
import jrain.fw.dao.common.handler.RowMapHandler;
import jrain.fw.dao.common.handler.RowMapListHandler;
import jrain.utils.exception.ServiceException;
import jrain.utils.lang.LangUtils;
import jrain.utils.lang.StringUtils;
import jrain.utils.sql.SqlUtils;

public class DefaultSqlRunner implements SqlRunner {

	private static final Logger log = LoggerFactory.getLogger("dao-runner");
	private final SqlParser sqlParser;
	private final TransactionManager transactionManager;
	private final String dataSourceName;
	private final int maxLimit;

	public DefaultSqlRunner(String dataSourceName) {
		this.sqlParser = FwUtils.getSqlParser();
		this.transactionManager = FwUtils.getTransactionManager();
		this.dataSourceName = dataSourceName;
		this.maxLimit = LangUtils.parseInt(FwUtils.getCfgService().getCfgValue(ServiceConst.M_DAO, "sql.max.limit", 1000));
	}

	protected Connection getConnection(Map<String, Object> paramData) {
		return transactionManager.getConnection(dataSourceName, paramData);
	}

	public int insert(String namespace, String statement, Map<String, Object> paramData) {
		SqlInfo sqlObj = sqlParser.generateInsert(namespace, statement, paramData);
		return execSql(paramData, sqlObj.getSql(), sqlObj.getParams().toArray());
	}

	public int update(String namespace, String statement, Map<String, Object> paramData) {
		SqlInfo sqlObj = sqlParser.generateUpdate(namespace, statement, paramData);
		return execSql(paramData, sqlObj.getSql(), sqlObj.getParams().toArray());
	}

	public int delete(String namespace, String statement, Map<String, Object> paramData) {
		SqlInfo sqlObj = sqlParser.generateDelete(namespace, statement, paramData);
		return execSql(sqlObj, paramData);
	}

	public int execSql(SqlInfo sqlObj, Map<String, Object> paramData) {
		return execSql(paramData, sqlObj.getSql(), sqlObj.getParams().toArray());
	}

	public int execSql(Map<String, Object> paramData, String sql, Object... params) {
		int rows = 0;
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			log.debug("exec method start");
			conn = getConnection(paramData);
			log.debug("exec prepre");
			log.debug("sql {}", sql);
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			log.debug("exec execute");
			rows = pstmt.executeUpdate();
			log.debug("exec method end");
		} catch (Exception e) {
			log.error("exec method error", e);
			throw new ServiceException(e.getMessage(), e);
		} finally {
			SqlUtils.closeStmt(pstmt);
			pstmt = null;
			closeConn(conn);
			conn = null;
		}
		return rows;
	}

	public Map<String, Object> query(String namespace, String statement, Map<String, Object> paramData) {
		SqlInfo sqlObj = sqlParser.generateQuery(namespace, statement, paramData);
		return query(sqlObj, paramData);
	}

	@Override
	public Map<String, Object> query(SqlInfo sqlObj, Map<String, Object> paramData) {
		return queryBySql(paramData, sqlObj.getMapper(), sqlObj.getSql(), sqlObj.getParams().toArray());
	}

	public int queryCount(String namespace, String statement, Map<String, Object> paramData) {
		Map<String, Object> bean = query(namespace, statement, paramData);
		Number count = null;
		if (bean != null) {
			count = (Number) bean.get("count");
		}
		return count == null ? 0 : count.intValue();
	}

	@Override
	public Map<String, Object> queryBySql(Map<String, Object> paramData, String sql, Object... params) {
		return queryBySql(paramData, new HashMap<>(), sql, params);
	}

	@Override
	public Map<String, Object> queryBySql(Map<String, Object> paramData, Map<String, Object> mapper, String sql, Object... params) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			log.debug("query method start");
			conn = getConnection(paramData);
			log.debug("query prepare");
			sql = sqlParser.generatePageSql(sql, 0, 1);
			log.debug("sql {}", sql);
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			log.debug("query execute");
			rs = pstmt.executeQuery();
			log.debug("query handle");
			IResultSetHandler<Map<String, Object>> handler = new RowMapHandler();
			Map<String, Object> rsMap = handler.handle(rs, mapper);
			log.debug("query method success");
			return rsMap;
		} catch (Exception e) {
			log.error("query method err", e);
			throw new ServiceException(e.getMessage(), e);
		} finally {
			SqlUtils.closeRs(rs);
			rs = null;
			SqlUtils.closeStmt(pstmt);
			pstmt = null;
			closeConn(conn);
			conn = null;
		}
	}

	public List<Map<String, Object>> list(String namespace, String statement, Map<String, Object> paramData) {
		paramData.put(SqlConst.IS_PAGE, SqlConst.NO);
		return listPage(namespace, statement, paramData).getRows();
	}

	@Override
	public List<Map<String, Object>> list(SqlInfo sqlObj, Map<String, Object> paramData) {
		return listPage(sqlObj, paramData).getRows();
	}

	public List<Map<String, Object>> listBySql(Map<String, Object> paramData, String sql, Object... params) {
		return listPageBySql(paramData, false, new HashMap<>(0), sql, null, -1, -1, params).getRows();
	}

	public List<Map<String, Object>> listBySql(Map<String, Object> paramData, Map<String, Object> mapper, String sql, Object... params) {
		return listPageBySql(paramData, false, mapper, sql, null, -1, -1, params).getRows();
	}

	public PageObj<List<Map<String, Object>>> listPage(String namespace, String statement, Map<String, Object> paramData) {
		SqlInfo sqlObj = sqlParser.generateQuery(namespace, statement, paramData);
		return listPage(sqlObj, paramData);
	}

	@Override
	public PageObj<List<Map<String, Object>>> listPage(SqlInfo sqlObj, Map<String, Object> paramData) {
		String sql = sqlObj.getSql();
		String countSql = sqlObj.getCountSql();
		List<Object> params = sqlObj.getParams();
		// 是否分页
		boolean isPage = true;
		String isPageStr = StringUtils.trimNull(paramData.get(SqlConst.IS_PAGE));
		if (!StringUtils.isEmpty(isPageStr) && !SqlConst.YES.equals(isPageStr)) {
			isPage = false;
		}
		// 限制返回结果集大小
		boolean isLimit = true;
		String isLimitStr = StringUtils.trimNull(paramData.get(SqlConst.IS_LIMIT));
		if (!StringUtils.isEmpty(isLimitStr) && !SqlConst.YES.equals(isLimitStr)) {
			isLimit = false;
		}
		// 当前页面
		int page = LangUtils.parseInt(paramData.get(SqlConst.PAGE), 0);
		// 分页大小
		int pageSize = LangUtils.parseInt(paramData.get(SqlConst.PAGE_SIZE), 10);
		int maxLimit = getMaxLimit();
		// 设置限制结果集
		if (isLimit && pageSize > maxLimit) {
			pageSize = maxLimit;
		}
		// 如果不分页，有结果集限制
		if (!isPage && isLimit) {
			isPage = true;
			pageSize = maxLimit;
		}
		// 计算开始数据行
		int startRow = page * pageSize;
		return listPageBySql(paramData, isPage, sqlObj.getMapper(), sql, countSql, startRow, pageSize, params.toArray());
	}

	@Override
	public PageObj<List<Map<String, Object>>> listPageBySql(Map<String, Object> paramData, String sql, long startRow, int pageSize, Object... params) {
		return listPageBySql(paramData, true, new HashMap<>(0), sql, null, startRow, pageSize, params);
	}

	@Override
	public PageObj<List<Map<String, Object>>> listPageBySql(Map<String, Object> paramData, Map<String, Object> mapper, String sql, long startRow, int pageSize,
			Object... params) {
		return listPageBySql(paramData, true, mapper, sql, null, startRow, pageSize, params);
	}

	protected PageObj<List<Map<String, Object>>> listPageBySql(Map<String, Object> paramData, boolean isPage, Map<String, Object> mapper, String sql,
			String countSql, long startRow, int pageSize, Object... params) {
		// 计算开始数据行
		int total = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (isPage) {
			if (StringUtils.isEmpty(countSql)) {
				countSql = sqlParser.generateCountSql(sql);
			}
			try {
				log.debug("list count start");
				conn = getConnection(paramData);
				log.debug("list count prepare");
				log.debug("sql {}", sql);
				pstmt = conn.prepareStatement(countSql);
				if (params != null) {
					for (int i = 0; i < params.length; i++) {
						pstmt.setObject(i + 1, params[i]);
					}
				}
				log.debug("list count execute");
				rs = pstmt.executeQuery();
				log.debug("list count handle");
				if (rs.next()) {
					Number obj = (Number) rs.getObject(1);
					if (obj != null) {
						total = obj.intValue();
					}
				}
				log.debug("list count end");
			} catch (Exception e) {
				log.error("list count error", e);
				throw new ServiceException(e.getMessage(), e);
			} finally {
				SqlUtils.closeRs(rs);
				rs = null;
				SqlUtils.closeStmt(pstmt);
				pstmt = null;
				closeConn(conn);
				conn = null;
			}
		}
		// ==============================================================================================
		try {
			log.debug("list query begin");
			conn = getConnection(paramData);
			log.debug("list query prepare");
			if (isPage) {
				sql = sqlParser.generatePageSql(sql, startRow, pageSize);
			}
			log.debug("sql {}", sql);
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			log.debug("list query execute");
			rs = pstmt.executeQuery();
			log.debug("list query handler");
			IResultSetHandler<List<Map<String, Object>>> handler = new RowMapListHandler();
			List<Map<String, Object>> list = handler.handle(rs, mapper);
			if (total == 0) {
				total = list.size();
			}
			log.debug("list query end");
			return new PageObj<List<Map<String, Object>>>(total, list);
		} catch (Exception e) {
			log.error("list query error", e);
			throw new ServiceException(e.getMessage(), e);
		} finally {
			SqlUtils.closeRs(rs);
			rs = null;
			SqlUtils.closeStmt(pstmt);
			pstmt = null;
			closeConn(conn);
			conn = null;
		}
	}

	protected int getMaxLimit() {
		return maxLimit;
	}

	public void closeConn(Connection conn) {
		transactionManager.closeConn(dataSourceName, conn);
	}

}
